package org.example.managere.repository.feibiao;

import org.example.managere.entity.feibiao.AmArchivesRelation;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Optional;

@Repository
public interface AmArchivesRelationRepository extends JpaRepository<AmArchivesRelation, Integer> {
    
    /**
     * 根据电机ID查询标准关联
     */
    List<AmArchivesRelation> findByMotorId_MotorId(String motorId);
    
    /**
     * 根据电机ID查询标准关联（分页）
     */
    Page<AmArchivesRelation> findByMotorId_MotorId(String motorId, Pageable pageable);
    
    /**
     * 根据标准类型查询标准关联
     */
    List<AmArchivesRelation> findByStandardType(String standardType);
    
    /**
     * 根据标准类型查询标准关联（分页）
     */
    Page<AmArchivesRelation> findByStandardType(String standardType, Pageable pageable);
    
    /**
     * 根据标准值查询标准关联
     */
    List<AmArchivesRelation> findByStandardValueContaining(String standardValue);
    
    /**
     * 根据标准值查询标准关联（分页）
     */
    Page<AmArchivesRelation> findByStandardValueContaining(String standardValue, Pageable pageable);
    
    /**
     * 根据关联日期范围查询标准关联
     */
    List<AmArchivesRelation> findByRelationDateBetween(Date startDate, Date endDate);
    
    /**
     * 查询特定电机和标准类型的关联
     */
    Optional<AmArchivesRelation> findByMotorId_MotorIdAndStandardType(String motorId, String standardType);
    
    /**
     * 查询所有唯一的标准类型
     */
    @Query("SELECT DISTINCT r.standardType FROM AmArchivesRelation r ORDER BY r.standardType")
    List<String> findAllDistinctStandardTypes();
    
    /**
     * 查询所有唯一的标准值
     */
    @Query("SELECT DISTINCT r.standardValue FROM AmArchivesRelation r ORDER BY r.standardValue")
    List<String> findAllDistinctStandardValues();
    
    /**
     * 查询满足指定标准类型和值的所有电机ID
     */
    @Query("SELECT r.motorId.motorId FROM AmArchivesRelation r WHERE r.standardType = :standardType AND r.standardValue = :standardValue")
    List<String> findMotorIdsByStandardTypeAndValue(@Param("standardType") String standardType, @Param("standardValue") String standardValue);
    
    /**
     * 按照标准类型分组统计数量
     */
    @Query("SELECT r.standardType, COUNT(r) FROM AmArchivesRelation r GROUP BY r.standardType ORDER BY COUNT(r) DESC")
    List<Object[]> countGroupByStandardType();
    
    /**
     * 统计每个电机满足的标准数量
     */
    @Query("SELECT r.motorId.motorId, COUNT(r) FROM AmArchivesRelation r GROUP BY r.motorId.motorId ORDER BY COUNT(r) DESC")
    List<Object[]> countStandardsByMotorId();
    
    /**
     * 检查特定电机是否符合指定标准
     */
    boolean existsByMotorId_MotorIdAndStandardType(String motorId, String standardType);
    
    /**
     * 根据电机ID和标准类型删除关联
     */
    @Modifying
    void deleteByMotorId_MotorIdAndStandardType(String motorId, String standardType);
    
    /**
     * 根据电机ID删除所有关联
     */
    @Modifying
    void deleteByMotorId_MotorId(String motorId);
    
    /**
     * 获取关联记录及电机信息（避免懒加载问题）
     */
    @Query("SELECT r FROM AmArchivesRelation r JOIN FETCH r.motorId")
    List<AmArchivesRelation> findAllWithMotorDetails();
    
    /**
     * 分页获取关联记录及电机信息
     */
    @Query(value = "SELECT r FROM AmArchivesRelation r JOIN FETCH r.motorId",
           countQuery = "SELECT COUNT(r) FROM AmArchivesRelation r")
    List<AmArchivesRelation> findAllWithMotorDetails(org.springframework.data.domain.Pageable pageable);
} 